/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tv.venta.dao;

import java.io.Serializable;
import java.math.BigDecimal;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import tv.global.dao.BaseDAO;
import tv.venta.modelo.Cliente;

/**
 *
 * @author ctrosch
 */
@Stateless
public class LimiteCreditoVentaDAO extends BaseDAO implements Serializable{

    public BigDecimal getLimiteCreditoDisponible(Cliente c) {

        try { String sQuery = "SELECT "
            + "ISNULL((SELECT VTTLCR_PORCEN FROM VTTLCR WHERE VTTLCR_CODCRD = VTMCLH_NROCTA),0) -"
            + "("
            + "	ISNULL((SELECT(SUM(VTRMVC_IMPNAC)) FROM VTRMVC WHERE VTRMVC_NROCTA = VTMCLH_NROCTA GROUP BY VTRMVC_NROCTA), 1) +"
            + "	ISNULL((SELECT Sum(IsNull(CJRMVI_IMPDEB,0)-IsNull(CJRMVI_IMPHAB,0)) FROM CJRMVI WHERE CJRMVI_NROCVT = VTMCLH_NROCTA AND CJRMVI_INDICA= 'C' AND CJRMVI_CTACTE NOT LIKE 'VRN%' GROUP BY CJRMVI_NROCVT), 0) +"
            + "	ISNULL((Select Sum(A.CJRMVI_IMPDEB) From CJRMVI A Where Exists (Select B.CJRMVI_CTACTE, B.CJRMVI_NROINT From CJRMVI B Where B.CJRMVI_FCHVNC > GetDate () "
            + "	And B.CJRMVI_NROCVT = VTMCLH_NROCTA And B.CJRMVI_CTACTE = A.CJRMVI_CTACTE And B.CJRMVI_NROINT = A.CJRMVI_NROINT AND B.CJRMVI_NROCPV IS NOT NULL "
            + "	Group By B.CJRMVI_CTACTE, B.CJRMVI_NROINT Having SUM(B.CJRMVI_IMPDEB - B.CJRMVI_IMPHAB) <> 0)),0) -"
            + "	ISNULL((SELECT SUM(FCRMVI_CANTID*FCRMVI_PRECIO + FCRMVI_CANTID*FCRMVI_PRECIO * FCRMVI_PCTBFN /100) "
            + "	FROM FCRMVI, FCRMVH WHERE FCRMVH_MODFOR = FCRMVI_MODAPL AND FCRMVH_CODFOR = FCRMVI_CODAPL "
            + "	AND FCRMVH_NROFOR = FCRMVI_NROAPL AND FCRMVH_CIRCOM = '0250' AND FCRMVH_NROCTA = VTMCLH_NROCTA), 1)"
            + ")"
            + "FROM VTMCLH WHERE VTMCLH_DEBAJA = 'N' "
            + "AND VTMCLH_NROCTA = '"+c.getNrocta()+"'";


            Query q = getEntityManager().createNativeQuery(sQuery);
            return (BigDecimal) q.getSingleResult();

        } catch(NoResultException nre){

            return BigDecimal.ZERO;

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("Error al obtener pendiente por item");
            return BigDecimal.ZERO;
        }
    }



    
    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
 
}
